Public Class NotesDAO
    Private Const SQL_VISTAPROFES As String = "select nom,id_persona from persona where tipus=2"
    Private Const SQL_VISTAASIGNATURES As String = "select nom,id_asignatura from asignatura where id_profesor=@1"
    Private Const SQL_VISTANOTES As String = "select * from vistanotes where id_asignatura=@1"
    'Per tenir la nota final de cada alumne
    Private Const SQL_VISTANOTES_ALUMNE As String = "select id_alumne,id_asignatura, avg(nota ) as NotaFinal from Notes group by id_alumne,id_asignatura"

    Public Shared Function Profes() As DataTable
        Dim adap As IDbDataAdapter
        Dim ds As DataSet
        Try

            adap = New OleDb.OleDbDataAdapter(SQL_VISTAPROFES, BaseDades.CadenaConnexio)
            ds = New DataSet
            adap.Fill(ds)
            Return ds.Tables(0)
        Catch ex As Exception
            Throw ex
        Finally
            'Buidar-ho tot
            adap = Nothing ' Aqui podem fer un dispose
            ds = Nothing ' aqui no podem fer un dispose, o el datatable desapareix
        End Try
        'Unicament ens interesa la taula

    End Function


    'Exercici 3
    Public Shared Function NotesAlumnes() As DataTable
        Dim adap As IDbDataAdapter
        Dim ds As DataSet
        Try

            adap = New OleDb.OleDbDataAdapter(SQL_VISTANOTES_ALUMNE, BaseDades.CadenaConnexio)
            ds = New DataSet
            adap.Fill(ds)
            Return ds.Tables(0)
        Catch ex As Exception
            Throw ex
        Finally
            'Buidar-ho tot
            adap = Nothing ' Aqui podem fer un dispose
            ds = Nothing ' aqui no podem fer un dispose, o el datatable desapareix
        End Try
        'Unicament ens interesa la taula

    End Function


    Public Shared Function ProfesAsignatures(ByVal idprofe As Int32) As DataTable
        Dim adap As IDbDataAdapter
        Dim ds As DataSet
        Try
            'Creem un command amb un paramete @1
            Dim bbdd As BaseDades = BaseDades.Instance
            Dim cmd As IDbCommand = New OleDb.OleDbCommand(SQL_VISTAASIGNATURES, bbdd.Connexio)
            Dim param As IDataParameter = cmd.CreateParameter()
            param.DbType = DbType.Int32
            param.ParameterName = "@1"
            param.Value = idprofe
            cmd.Parameters.Add(param)
            'Creem un dataadapter amb el command, unicament es per fer selects
            adap = New OleDb.OleDbDataAdapter(cmd)
            ds = New DataSet
            adap.Fill(ds)
            Return ds.Tables(0)
        Catch ex As Exception
            Throw ex
        Finally
            'Buidar-ho tot
            adap = Nothing ' Aqui podem fer un dispose
            ds = Nothing ' aqui no podem fer un dispose, o el datatable desapareix
        End Try
        'Unicament ens interesa la taula

    End Function
    Public Shared Function NotesAsignatures(ByVal idasignatura As Int32) As DataTable
        Dim adap As IDbDataAdapter
        Dim ds As DataSet
        Try
            'Creem un command amb un paramete @1
            Dim bbdd As BaseDades = BaseDades.Instance
            Dim cmd As IDbCommand = New OleDb.OleDbCommand(SQL_VISTANOTES, bbdd.Connexio)
            Dim param As IDataParameter = cmd.CreateParameter()
            param.DbType = DbType.Int32
            param.ParameterName = "@1"
            param.Value = idasignatura
            cmd.Parameters.Add(param)
            'Creem un dataadapter amb el command, unicament es per fer selects
            adap = New OleDb.OleDbDataAdapter(cmd)
            ds = New DataSet
            adap.Fill(ds)
            Return ds.Tables(0)
        Catch ex As Exception
            Throw ex
        Finally
            'Buidar-ho tot
            adap = Nothing ' Aqui podem fer un dispose
            ds = Nothing ' aqui no podem fer un dispose, o el datatable desapareix
        End Try
        'Unicament ens interesa la taula

    End Function


End Class
